﻿CREATE FUNCTION [RestartFramework].[GetETLJobStagesPerETLJobNameETLJobStageOrder]
(
	@pETLJobName		SYSNAME
,	@pETLJobStageOrder	SMALLINT	=	NULL
,	@pMaxThreads		TINYINT		=	8
)
RETURNS TABLE AS RETURN
(
	SELECT	ejs.[ETLJobStageId]
	,		ejs.[ETLJobStageName]
	,		ejs.[ETLJobStageEnabled]
	,       ejs.[ETLJobStageOrder]
	/*I want the threads to be 0-based, hence why I subtract 1 in the following expression
	Jamie Thomson, 2012-03-13

    The PARTITION BY isn't necessary because we're already filtering by @pETLJobName. However, I'm putting it in to protect
    against future changes that someone might make that might unwittingly break it.
    
	Jamie Thomson, 2012-05-15
	*/
	,		[ThreadId]	=	(ROW_NUMBER() OVER (PARTITION BY ej.[ETLJobName] ORDER BY ejs.[ETLJobStageName]) -1) % @pMaxThreads
	,		ejs.[ETLJobStageIsComplete]
	FROM	[RestartFramework].[ETLJob] ej
	INNER JOIN [RestartFramework].[ETLJobStage] ejs		ON	ej.[ETLJobId]	=	ejs.[ETLJobId]
	WHERE	ej.[ETLJobName]			=	@pETLJobName
		AND	(	ejs.[ETLJobStageOrder]	=	@pETLJobStageOrder
			OR	@pETLJobStageOrder		IS NULL
			)
)
